package hn.cch.springboot_datasource.dao.impl;

import hn.cch.springboot_datasource.dao.UserDao;
import hn.cch.springboot_datasource.entity.User;
import oracle.jdbc.OracleTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository("userOracleDaoImpl")
public class UserOracleDaoImpl implements UserDao {


    @Autowired
    @Qualifier("oracleJdbcTemplate")
    private JdbcTemplate oracleJdbcTemplate;

    public User toUser(ResultSet resultSet) throws SQLException {
        User user = new User();
        user.setUserId(resultSet.getInt("user_id"));
        user.setUserName(resultSet.getString("user_name"));
        user.setNumber(resultSet.getInt("number"));
        user.setPassword(resultSet.getString("password"));
        return user;
    }

    public int insertUser(User user) {
        String sql = "insert into tbl_user(user_name, \"number\", password) values (?,?,?)";
        Object[] params = new Object[]{user.getUserName(), user.getNumber(), user.getPassword()};
        int row = oracleJdbcTemplate.update(sql, params);
        return row;
    }


    @Override
    public List<User> selectAllUserList() {

        String sql = "select user_id, user_name, \"number\", password from tbl_user";
        List<User> userList = oracleJdbcTemplate.query(sql, (resultSet, i) -> toUser(resultSet));
        return userList;
    }

    @Override
    public List<User> callUser(String userName) {
        int outIndex = 2;
        return oracleJdbcTemplate.execute(new CallableStatementCreator() {

            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {

                String sql = "{call sp_user(?,?)}";
                CallableStatement callableStatement = connection.prepareCall(sql);
                callableStatement.setString(1, userName);
                callableStatement.registerOutParameter(outIndex, OracleTypes.CURSOR);
                return callableStatement;
            }
        }, new CallableStatementCallback<List<User>>() {
            @Override
            public List<User> doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
                List<User> userList = new ArrayList<>();
                callableStatement.execute();
                Object object = callableStatement.getObject(outIndex);
                if (object instanceof ResultSet){
                    ResultSet resultSet = (ResultSet) object;
                    while (resultSet.next()){
                        User user = toUser(resultSet);
                        userList.add(user);
                    }
                }
                return userList;
            }
        });

    }
}
